package com.bing.beans;

import oracle.jdbc.pool.OracleDataSource;

import java.sql.*;

/**
 * Created by kaibb on 14-7-26.
 */
public class DataBaseHandler {
    private String url = "jdbc:oracle:thin:";
    private Connection connection;
    private OracleDataSource ds;
    private Statement statement;
    private PreparedStatement pStatement;
    private ResultSet resultSet;
    private String sql;

    public DataBaseHandler(String userName, String passWord, int port) throws SQLException {
        this.url = url + userName + "/" + passWord + "@localhost:" + port + "/XE";
        ds = new OracleDataSource();
        ds.setURL(url);
        getDBConnection();

    }

    public DataBaseHandler() throws SQLException {
        ds = new OracleDataSource();
        ds.setURL("jdbc:oracle:thin:hr/HR@//localhost:1522/XE");
        getDBConnection();

    }

    public Connection getDBConnection() throws SQLException {
        connection = ds.getConnection();
        return connection;
    }

    public ResultSet getAllEmployee() throws SQLException {
        statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        sql = "SELECT * FROM employees ORDER BY employee_id ";
        resultSet = statement.executeQuery(sql);
        return resultSet;
    }

    public ResultSet getAllEmployeeByName(String name) throws SQLException {

        //bingsay 这里like 必须要用?号..% %在设置值的时候加上去.否则会报错
        sql = "select * from employees where first_name like ? order by employee_id ";
        pStatement = connection.prepareStatement(sql);
        System.out.println("getAllEmployeeByName3: " + sql);
        pStatement.setString(1, "%" + name + "%");
        resultSet = pStatement.executeQuery();
        return resultSet;
    }
}